﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BookStore.Common;
using System.Data.SqlClient;
using BookStore.Models;
using System.Data;
using System.Windows.Forms;

namespace BookStore.Controllers
{
    public class CategoryController : Controller
    {
        private static CategoryController _controller = null;
        public static CategoryController getController()
        {
            if (_controller == null)
                return new CategoryController();
            else
                return _controller;
        }
        public CategoryController()
        {
            _controller = this;
        }

        internal object getAllCategory()
        {
            DataTable dt = new DataTable();
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("Select * from Category", con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
            }
            finally
            {
                con.Close();
            }
            return dt;
        }

        public String getCategory(String category, int check)
        {
            DataTable dt = new DataTable("GetCategory");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Get CategoryName from CategoryID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_GetCategoryName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = category;
                }
                // Get CategoryID from CategoryName, check = 1
                else
                {
                    cmd = new SqlCommand("BS_GetCategoryID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = category;
                }
                var result = cmd.ExecuteReader();
                dt.Load(result);
                if(dt.Rows.Count > 0)
                    return dt.Rows[0][0].ToString();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return "";
        }

        public void AddCategoryToDatabase(String CategoryID, String CategoryName, String CatalogueID)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_AddCategoryToDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = CategoryID;
                cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = CategoryName;
                cmd.Parameters.Add("@CatalogueID", SqlDbType.NVarChar).Value = CatalogueID;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void DeleteCategoryFromDatabase(String CategoryID)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_DeleteCategoryFromDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = CategoryID;
                cmd.ExecuteNonQuery();
                tran.Commit();
                MessageBox.Show("Deleted successful!", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
                MessageBox.Show("You don't permission to delete this category because it's attribute!", "Error : Delete Category", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void ModifyCategoryFromDatabase(String CategoryID, String CategoryName, String CatalogueID)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_ModifyCategoryFromDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = CategoryID;
                cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = CategoryName;
                cmd.Parameters.Add("@CatalogueID", SqlDbType.NVarChar).Value = CatalogueID;
                cmd.ExecuteNonQuery();
                tran.Commit();
                MessageBox.Show("Modified successful!", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
                MessageBox.Show("You don't permission to modify this category because it's attribute!", "Error : Modify Category", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public DataTable DisplayCategory(String Category, int check)
        {
            DataTable dt = new DataTable("DisplayCategory");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Display Category From CategoryID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_DisplayCategoryFromID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = Category;
                }
                // Display Category From CategoryName, check = 1
                else if (check == 1)
                {
                    cmd = new SqlCommand("BS_DisplayCategoryFromName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = Category + "%";
                }
                // Display Category like CategoryID, check = 2
                else
                {
                    cmd = new SqlCommand("BS_DisplayCategoryLikeID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = Category + "%";
                }
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public DataTable DisplayCategoryFromCatalogueID(String CatalogueID)
        {
            DataTable dt = new DataTable("DisplayCategory");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("BS_DisplayCategoryFromCatalogueID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@CatalogueID", SqlDbType.NVarChar).Value = CatalogueID;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public DataTable DisplayAllCategory()
        {
            DataTable dt = new DataTable("DisplayAllCategory");
            String sql = @"SELECT [CategoryID],[CategoryName],[CatalogueID]
                           FROM [BookStore].[dbo].[CATEGORY]";
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public Boolean CheckCategoryInDatabase(String Category, int check)
        {
            DataTable dt = new DataTable("CheckCategoryInDatabase");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Check Category From CategoryID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_CheckCategoryFromID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = Category;
                }
                // Check Category From CategoryName, check = 1
                else
                {
                    cmd = new SqlCommand("BS_CheckCategoryFromName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = Category;
                }
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                if (dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return false;
        }

        public Boolean checkCategoryName(String CategoryName)
        {
            for (int i = 0; i < CategoryName.Length; i++)
            {
                if ((CategoryName[i].ToString() == "\\") || (CategoryName[i].ToString() == ";") || (CategoryName[i].ToString() == ")"))
                    return false;
            }
            return true;
        }

        internal object SearchByID(string p)
        {
            DataTable dt = new DataTable();
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("Select * from Category where CategoryID like '" + p + "%';", con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
            }
            finally
            {
                con.Close();
            }
            return dt;
        }
        internal object SearchByByName(string p)
        {
            DataTable dt = new DataTable();
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("Select * from Category where CategoryName like '" + p + "%';", con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
            }
            finally
            {
                con.Close();
            }
            return dt;
        }
    }
}
